package gen.core.managers;

import gen.core.exceptions.GenException;
import gen.core.models.Sql;
import gen.core.others.GenBeanProcess;

import java.sql.Connection;
import java.util.Date;
import java.util.List;
import java.util.UUID;

import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateFormatUtils;

public class SqlManager {
	/**
	 * 通过模版ID，查询所有对应的Sql语句对象
	 * 
	 * @param conn
	 * @param templateId
	 * @return
	 */
	public Sql[] list(Connection conn, String templateId) {
		String sql;
		QueryRunner runner = new QueryRunner();
		
		BeanListHandler<Sql> handler = new BeanListHandler<Sql>(Sql.class, new BasicRowProcessor(new GenBeanProcess()));
		sql = "SELECT * FROM GEN_SQL WHERE TEMPLATE_ID = ?";
		try {
			List<Sql> aList = runner.query(conn, sql, handler, new Object[] { templateId });
			return aList.toArray(new Sql[0]);
		} catch (Exception ex) {
			throw new GenException(ex, "通过模版ID，查询所有对应的Sql语句对象时，发生异常！");
		}
	}
	
	/**
	 * 按Sql语句的ID，查询Sql语句对象
	 * 
	 * @param conn
	 * @param id
	 * @return
	 */
	public Sql load(Connection conn, String id) {
		String sql;
		QueryRunner runner = new QueryRunner();

		BeanHandler<Sql> handler = new BeanHandler<Sql>(Sql.class, new BasicRowProcessor(new GenBeanProcess()));
		sql = "SELECT * FROM GEN_SQL WHERE ID = ?";
		try {
			return runner.query(conn, sql, handler, new Object[] { id });
		} catch (Exception ex) {
			throw new GenException(ex, "按SQL语句的ID，查询SQL语句对象，发生异常！SQL语句ID=" + id + "！");
		}
	}
	
	/**
	 * 保存Sql语句对象到数据库。如果sql语句对象包含id属性，则执行update操作；反之，执行insert操作。
	 * 
	 * @param conn
	 * @param theSql
	 */
	public void save(Connection conn, Sql theSql) {
		String sql;
		QueryRunner runner = new QueryRunner();

		try {
			if (StringUtils.isBlank(theSql.getId())) {
				sql = "INSERT INTO GEN_SQL VALUES(?,?,?,?,?,?,?,?,?)";
				theSql.setId(UUID.randomUUID().toString());
				theSql.setCreateTime(DateFormatUtils.format(new Date(), "yyyy-MM-dd HH:mm:ss"));
				theSql.setModifiedTime(DateFormatUtils.format(new Date(), "yyyy-MM-dd HH:mm:ss"));
				runner.update(conn, sql, theSql.getId(), theSql.getName(), theSql.getTemplateId(), theSql.getDescription(), theSql.getCatagory(),
						theSql.getContent(), theSql.getOwner(), theSql.getCreateTime(), theSql.getModifiedTime());
			} else {
				theSql.setModifiedTime(DateFormatUtils.format(new Date(), "yyyy-MM-dd HH:mm:ss"));
				sql = "UPDATE GEN_SQL SET NAME=?, TEMPLATE_ID=?, DESCRIPTION=?, CATAGORY=?, CONTENT=?, MODIFIED_TIME=? WHERE ID = ?";
				runner.update(conn, sql, theSql.getName(), theSql.getTemplateId(), theSql.getDescription(), theSql.getCatagory(), theSql.getContent(),
						theSql.getModifiedTime(), theSql.getId());
			}
		} catch (Exception ex) {
			throw new GenException(ex, "保存SQL语句时，发生异常！");
		}
	}
	
	/**
	 * 按Sql语句的ID，从数据库删除Sql语句对象。
	 * 
	 * @param conn
	 * @param id
	 */
	public void delete(Connection conn, String id) {
		String sql;
		QueryRunner runner = new QueryRunner();

		sql = "DELETE FROM GEN_SQL WHERE ID = ?";
		try {
			runner.update(conn, sql, id);
		} catch (Exception ex) {
			throw new GenException(ex, "删除SQL语句时，发生异常！SQL语句ID=" + id + "！");
		}
	}
}
